package ca.ualberta.cs.CLRDB;

/*******************************************************************************
*
*  * Copyright (C) 2010, 20?? - University of Alberta
*  * This program is free software; you can redistribute it and/or modify it under the terms
*  * of the GNU General Public License as published by the Free Software Foundation; either version 3 of the License,
*  * or (at your option) any later version.
*  * This program is distributed in the hope that it will be useful, but WITHOUT ANY WARRANTY; without even the implied
*  * warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details.
*  * You should have received a copy of the GNU General Public License along with this program.  If not, see <http://www.gnu.org/licenses/>.
*  *
*  * Author: Samaneh Bayat
*  * Email: samaneh@ualberta.ca
* 
*******************************************************************************/

import java.io.IOException;
import java.lang.reflect.Method;
import java.sql.*; 
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Random;

import ca.ualberta.cs.BibTex.BibTex;
import ca.ualberta.cs.CLRDB.User;
import ca.ualberta.cs.CLRDB.interfaces.AnnotationAccess;
import ca.ualberta.cs.CLRDB.interfaces.EntityRelManagement;
import ca.ualberta.cs.DB2ExceptionHandling.DB2SqlException;

import javax.swing.JOptionPane;

import com.ibm.db2.jcc.uw.DB2Exception;

import uk.ac.liv.c3connector.AnnotationModel;

public class AnnotationDatabase extends EntityRelManagement implements AnnotationAccess{

	//Assuming Driver type 4 for the JDBC
	

	private static Random rand = new Random();
	
	private static ArrayList<String> annoDBFields = new ArrayList<String>();
   
	//private Connection con;
	
	/*protected static AnnotationDatabase singleton;

	public static synchronized AnnotationDatabase getInstance(){
		if(singleton != null)
			return singleton;
		else
			return new AnnotationDatabase();
	}*/
	
   public AnnotationDatabase(){	   
		   
		/*personsMgmt = new PersonsManagement();
		resourceMgmt = new ResourceManagement();*/
		
		//annoDBFields.add("ID"); 
		 //
	   
	   mainTableName = "Annotation";
	   
	   annoDBFields.clear();
		annoDBFields.add("dateCreated"); annoDBFields.add("dateModified"); //TODO: maybe I should remove datemodified from this list
		annoDBFields.add("annotationBody"); annoDBFields.add("stringDescription");
		annoDBFields.add("annotationUri"); annoDBFields.add("lexicalSignature"); 
		/*annoDBFields.add("documentDigest");
		annoDBFields.add("documentTextDigest");*/ 
		annoDBFields.add("pageNumber"); annoDBFields.add("application");
		annoDBFields.add("version"); annoDBFields.add("resourceId"); 
		//annoDBFields.add("userid"); //annoDBFields.add("author");
		annoDBFields.add("personId");
		annoDBFields.add("annoFABid"); annoDBFields.add("replyTo"); annoDBFields.add("replyToFabId");
   }
   

   
   /**
    * clears everything in the db and creates tables
 * @throws SQLException 
    */
   public void initialize(Statement statement) throws SQLException{
	   //FIXME create database and user pass
	   //FIXME create triggers
	   
	   try{
			statement.executeUpdate("DROP TABLE Annotation");
	   }catch(Exception e){}
		
			//following table is almost taken from AnnotationModel.java
			statement.executeUpdate("CREATE TABLE Annotation ( "+ 
					"ID INTEGER GENERATED ALWAYS AS " +
						"IDENTITY (START WITH 1 INCREMENT BY 1) NOT NULL, " +
					"dateCreated Date, " +
					"dateModified Date, " + //TODO can I put a procedure for it to be updated automatically?					
					"annotationBody CLOB(50K), " + //or,   FIXME check in fab4, if anno was getting large, don't let more
							//"annotationBody LONG VARCHAR(32699), " +
					"stringDescription VARCHAR(3000), " + //what is it?!
					"annotationUri VARCHAR(3000), " +
					"lexicalSignature VARCHAR(3000), " + //what?					
					"pageNumber INTEGER, " +
					"application VARCHAR(300), " + //why do we have this?
					"version VARCHAR(300), " + //why do we have this?
					"resourceId INTEGER NOT NULL, " + //TODO check this. should it be FK?
					//"author VARCHAR(300) NOT NULL, " +
					"personId INTEGER NOT NULL, " +
					"annoFABid VARCHAR(300) NOT NULL," +
					"replyTo INTEGER, " + //TODO: UPDATE NO ACTION
					"replyToFabId VARCHAR(300), " +
					"hasReply CHAR(1) NOT NULL," +
							//I do not keep: isReplyToSth
					"CONSTRAINT id_uniq PRIMARY KEY(ID), " +
					"CONSTRAINT FK_reply FOREIGN KEY (replyTo) " +
                            "REFERENCES Annotation (ID) ON DELETE CASCADE, " + //
                    "CONSTRAINT FK_personid FOREIGN KEY (personId) " +
                            "REFERENCES Person (ID) ON DELETE CASCADE, " + //TODO: an author is deleted only if he wants to remove all his comments
                    "CONSTRAINT FK_resourceid FOREIGN KEY (resourceId) " +
                            "REFERENCES Resource (ID) ON DELETE CASCADE " +
					") IN CLRSPACE");
			numOfCols = 16;
			
			statement.executeUpdate("ALTER TABLE Annotation " +
	   				"ADD COLUMN " +
	   					"type VARCHAR(10) " +
	   				"ADD COLUMN " +
	   					"rateByAnno SMALLINT " + //for notes: 1:pos, 2:neg, 3:neutral, 4:informative, 5:no category
//	   				"ADD CONSTRAINT rateValue CHECK (rateByAnno < 6 AND rateByAnno > 0 )" +
	   				"");
			
			/*
			 * statement.executeUpdate("ALTER TABLE Annotation " +
	   				"ADD COLUMN " +
	   					"type VARCHAR(10) " +
	   				"ADD COLUMN " +
	   					"rateByAnno SMALLINT " + //for notes: 1:pos, 2:neg, 3:neutral, 4:informative, 5:no category
//	   				"ADD CONSTRAINT rateValue CHECK (rateByAnno < 6 AND rateByAnno > 0 )" +
	   				"");
			 */
			
			numOfCols += 2;
	   }
  

   /**
    * @param am (AnnotationModel)
    * @param username
    * @param pass
    * @return  0: ok, 1: user not authenticated, 2: exception
    */
   public int storeOrUpdateAnyAnnotation(AnnotationModel am, String username, String pass){
	   
	   HashMap<String, String> auth = DatabaseController.personsMgmt.authenticate(username, pass);
	   int state = Integer.parseInt(auth.get("state"));
	   if( state != 0 )
		   return 1;
	   
	   //public anno has the username of the one authenticated with
	   //trusting the one sitting on the private computer!!!
	   if(! username.equals(am.getUserid()) ){
		   System.out.println("Warning: This annotation has a different user id than the one authenticated with! It will be published with the authenticated user id.");
		   am.setUserid(username);
	   }	   	
	
	   if(am.getResourceUri().startsWith("file:/"))
			am.setResourceUri(username+ ":" +am.getResourceUri());		
	  
	   int annotype = storeOrUpdateIfNotComment(am, username);
	   if(annotype == -1)
		   return storeOrUpdateAnnotation(am, username, pass, true);
	   else 
		   return annotype;
   }

   /**
    * @param am (AnnotationModel)
    * @param username
    * @return  0: ok,  -1: if it is a comment
    */
   public int storeOrUpdateIfNotComment(AnnotationModel am, String username){
	   //a tag annotation
	   if(am.getAnnotationBody().trim().startsWith("<tag")){
		   DatabaseController.resourceMgmt.getTagsManagement().addTagging(am, username);
		   return 0;
	   }
	   
	 //a rate annotation
	   if(am.getAnnotationBody().trim().startsWith("<rate")){
		   DatabaseController.rateMgmt.updateRateResource(am, username);
		   return 0;
	   }
	   
	 //a bibtex info annotation
	   if(am.getAnnotationBody().trim().startsWith("<bibtex")){
		   ResourceManagement.bibtexMgmt.insertNewBibtexInformation(am, username);
		   return 0;
	   }

	   return -1;
   } 
   
   /**
    * @param am (AnnotationModel)
    * @param username
    * @param pass
    * @return  0: ok, 1: user not authenticated, 2: exception
    */
   public int storeOrUpdateAnnotation(AnnotationModel am, String username, String pass, boolean hasCheckedOtherTypes){
   	
	   if(!hasCheckedOtherTypes){
		   return storeOrUpdateAnyAnnotation(am, username, pass);		   
	   }
/*	   HashMap<String, String> auth = DatabaseController.personsMgmt.authenticate(username, pass);
	   int state = Integer.parseInt(auth.get("state"));
	   if( state != 0 )
		   return 1;
	   
	   //public anno has the username of the one authenticated with
	   //trusting the one sitting on the private computer!!!
	   if(! username.equals(am.getUserid()) ){
		   System.out.println("Warning: This annotation has a different user id than the one authenticated with! It will be published with the authenticated user id.");
		   am.setUserid(username);
	   }	   	
	
	   if(am.getResourceUri().startsWith("file:/"))
			am.setResourceUri(username+ ":" +am.getResourceUri());		
	   
	   //a tag annotation
	   if(am.getAnnotationBody().trim().startsWith("<tag")){
		   DatabaseController.resourceMgmt.getTagsManagement().addTagging(am, username);
		   return 0;
	   }
	   
	 //a tag annotation
	   if(am.getAnnotationBody().trim().startsWith("<rate")){
		   DatabaseController.rateMgmt.updateRateResource(am, username);
		   return 0;
	   }
	   
	 //a bibtex info annotation
	   if(am.getAnnotationBody().trim().startsWith("<bibtex")){
		   ResourceManagement.bibtexMgmt.insertNewBibtexInformation(am, username);
		   return 0;
	   }
	   
*/	   
   	Connection con = DatabaseController.connectToDB();
   	Statement statement;
   	int ret = 0;
   	
   	am = retrieveReplyOnInfo(am, con);
   	
		try {
			statement = con.createStatement();		
			
			boolean exists = false;
			Integer id = null;
			Integer resourceId = null;
			Integer personId = null;
			
			
			if(am.getUniqueId() != null){
			
				ResultSet result = statement.executeQuery("SELECT ID, personId, resourceId, annotationBody FROM Annotation WHERE annoFABid = '"+ am.getId() +"'" );			
				//ID, userid, resourceUri, annotationBody
				
				int num = 0;
				
				
				String userid = "";
				String resourceUri = "";
				String annobody = "";			
				
				while( result.next() ){
				//if( result.next() ){
					num++;
					
					id = result.getInt("ID");
					resourceId = result.getInt("resourceId");
					personId = result.getInt("personId");
					annobody = result.getString("annotationBody");
					
					Statement st2 = con.createStatement();
					ResultSet res = st2.executeQuery("SELECT url FROM Resource WHERE ID = "+ resourceId ) ; 
					res.next();
					resourceUri = res.getString("url");
					st2.close();
					
					Statement st3 = con.createStatement();
					ResultSet resP = st3.executeQuery("SELECT username FROM Person WHERE ID = "+ personId ) ; 
					resP.next();									
					userid = resP.getString("username");
					st3.close();
					
					if(am.getResourceUri().equals(resourceUri) 
							&& am.getUserid().equals(userid)
							&& am.getAnnotationBody().equals(annobody))
						return 0; //no need to update, FIXME it is just about another resource uri which is not yet reloaded and the user doesn't see that it is already published					
				}
							
				if( num != 0){
					exists = true;					
					id = am.getUniqueId();
				}
				//if( num == 1 )	//if the one in db is this am, otherwise num was greater than 1 or uniqueid was null
				//else if(num != 0) //if more than 1 with this fabid //not supposed to happen!										
			}
			else{
				//something with this fabid exists, but it is not the same as this am
				//exists = false;	//since if they were the same, this 'am' had its ID from DB
			}
			
			if( exists ){ //UPDATE
				ret = updateAnno(am, resourceId, personId, id, con);
			}
			else{ //INSERT
				//find resourceId and personId		
				if(am.getResourceId() != null ){
					resourceId = am.getResourceId(); //already exists (may bib have been set or not, anyway it doesn't change in time of publishing annos!)				
					//TODO but I have not thought about copy to server					
				}
				/*else if(bib != null){
					resourceId = resourceMgmt.resourceExists(bib, am.resourceUri, con);
					if(resourceId == -1) //not exists
						resourceId = resourceMgmt.addResource(bib, am.resourceUri,con);	
				}*/
				else{
					resourceId = DatabaseController.resourceMgmt.resourceExists(am.resourceUri, con);
					if(resourceId == -1) //not exists
						resourceId = DatabaseController.resourceMgmt.addResource(am.resourceUri,am.documentDigest,am.documentTextDigest,con);
				}
				//TODO : may url be the same, but digest change? -> dynamic urls, like google.com . take care later
				DatabaseController.resourceMgmt.updateResourceDigest(resourceId, am.documentDigest,am.documentTextDigest,con);
				
				ResultSet resU = statement.executeQuery("SELECT ID FROM Person WHERE username = '" + username + "'");
				resU.next();
				personId = resU.getInt("ID");
							
				ret = insertAnno(am, resourceId, personId, con);
			}
 
			statement.close();
			con.commit();
			
		} catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();
				//if(e.getErrorCode() == -4470 )
				//FIXME I think I should restart db2 driver?
				e = e.getNextException();
			}	
			return 2;
		} catch (Exception e) {			
			e.printStackTrace();
			return 2;
		} 
		return ret;
   }
   
   /**
    * The return values should keep compatible with storeOrUpdateAnno()'s return values
    * @param am
    * @param resourceId
    * @param personId
    * @param id
    * @param con
    * @return 0: ok, 2: exception
    */
   private int updateAnno(AnnotationModel am, Integer resourceId, Integer personId, Integer id, Connection con){
	   
	 try{  
	   Statement statement = con.createStatement();
	   
		String query = "UPDATE Annotation SET (";
		
		for(int i = 0 ; i < annoDBFields.size() ; i++ ){
			if( !annoDBFields.get(i).equals("annoFABid") && !annoDBFields.get(i).equals("resourceId") && !annoDBFields.get(i).equals("personId") )
				if( am.getClass().getDeclaredField(annoDBFields.get(i)).get(am) == null )
					continue;
			if(am.isReplyToSth || (!annoDBFields.get(i).equals("replyTo") && !annoDBFields.get(i).equals("replyToFabId")))
				query += annoDBFields.get(i) + "," ;
		}
		
		query = query.substring(0, query.length()-1); //remove last ','
		
		query += ") = (";
		for(int i = 0 ; i < annoDBFields.size() ; i++ ){ //TODO take care of this in future, it is not complete
			
			if(!am.isReplyToSth && (annoDBFields.get(i).equals("replyTo") || annoDBFields.get(i).equals("replyToFabId") ))						
				continue; //make the 'replyTo' field equal to 'NULL'
							//assuming no body can update a 'reply' anno to a not reply anno!
			
			Object obj;
			if(annoDBFields.get(i).equals("annoFABid"))
				obj = am.getClass().getDeclaredField("id").get(am);
			else if(annoDBFields.get(i).equals("resourceId"))
				obj = resourceId;
			else if(annoDBFields.get(i).equals("personId"))
				obj = personId;
			else
				obj = am.getClass().getDeclaredField(annoDBFields.get(i)).get(am);
			
			if( obj == null )
				//query += "NULL" +",";
				continue;
			else if( obj instanceof String )
				query += "'" + ((String)obj).replace("'", "`") + "',";
			else if( obj instanceof Integer )
				query += Integer.toString((Integer)obj) + ",";
			else if( obj instanceof Date )
				query += "'" + (new java.sql.Date(((Date)obj).getTime())).toString() +"'" + ",";
			else
				query += obj.toString(); //TODO
		}
		query = query.substring(0, query.length()-1); //remove last ','
		//query += ") WHERE ID = '" + am.getId() +"'" ;
		query += ") WHERE ID = " + id +"" ;
		
		System.out.println();
		System.out.println(query);
		System.out.println();
		
		statement.executeUpdate(query);
		statement.close();
		con.commit();

	 } catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();
				//if(e.getErrorCode() == -4470 )
				//FIXME I think I should restart db2 driver?
				e = e.getNextException();
			}	
			return 2;
		} catch (Exception e) {			
			e.printStackTrace();
			return 2;
		} 
		return 0;
   }
   
   /**
    * The return values should keep compatible with storeOrUpdateAnno()'s return values
    * @param am
    * @param resourceId
    * @param personId
    * @param con
    * @return 0: ok, 2: exception
    */
   private int insertAnno(AnnotationModel am, Integer resourceId, Integer personId, Connection con){
	   try{
		   HashMap<String,String> attr = ContentSerializer.parseAttributes(am.getAnnotationBody());
		   
		   Statement statement = con.createStatement();
			
			String query = "INSERT INTO ANNOTATION (";
			for(int i = 0 ; i < annoDBFields.size() ; i++ ){
				if( !annoDBFields.get(i).equals("annoFABid") && !annoDBFields.get(i).equals("resourceId") && !annoDBFields.get(i).equals("personId") ){
					if( am.getClass().getDeclaredField(annoDBFields.get(i)).get(am) == null )						
						continue;
				}				
				if(am.isReplyToSth || (!annoDBFields.get(i).equals("replyTo") && !annoDBFields.get(i).equals("replyToFabId")))
					query += annoDBFields.get(i) + "," ;
			}
			
			//query = query.substring(0, query.length()-1); //remove last ','
			query += "hasReply, type";
			
			//rate of resource content, given by annotation: positive, negative, neutral, informative, none
			if(attr.get("rated") != null)
				query += ",rateByAnno";
			
			int snapshotId = -1;
			if(attr.get("textsnapshot") != null){
				String section = attr.get("relatedSection2");
				section = section == null ? "" : section;
				String preSection = attr.get("relatedSection1");
				preSection = preSection == null ? "" : preSection;
				snapshotId = DatabaseController.textsnapMgmt.insertTextSnapshot(resourceId, section, preSection, attr.get("textsnapshot"), statement);
				
				//to decrease the size of annotation body which will be saved to db
				//annotation body no longer needs textsnapshot info. and in loading annotations in the fab4 browser, this information will not be retrieved. So, no need to keep it.
				ArrayList<String> toRemove = new ArrayList<String>();
				toRemove.add("textsnapshot");
				toRemove.add("relatedSection1");
				toRemove.add("relatedSection2");
				am.setAnnotationBody( ContentSerializer.removeAttributeFromAnnotationBody(am.getAnnotationBody(), toRemove) );
				
				System.out.println("Annotation body size: "+ am.getAnnotationBody().length());
			}
			if( snapshotId != -1)
				query += ",textsnapshot";
			
			//add annotation fields taken from AnnotationModel
			query += ") VALUES (";
			for(int i = 0 ; i < annoDBFields.size() ; i++ ){
									
				if(!am.isReplyToSth && (annoDBFields.get(i).equals("replyTo") || annoDBFields.get(i).equals("replyToFabId") ))
					continue; //make the 'replyTo' field equal to 'NULL'
				
				Object obj;
				if(annoDBFields.get(i).equals("annoFABid"))
					obj = am.getClass().getDeclaredField("id").get(am);
				else if(annoDBFields.get(i).equals("resourceId"))
					obj = resourceId;
				else if(annoDBFields.get(i).equals("personId"))
					obj = personId;
				else
					obj = am.getClass().getDeclaredField(annoDBFields.get(i)).get(am);
				
				if( obj == null )
					//query += "NULL" +",";
					continue;
				else if( obj instanceof String )
					query += "'" + ((String)obj).replace("'", "`") + "',";
				else if( obj instanceof Integer )
					query += Integer.toString((Integer)obj) + ",";
				else if( obj instanceof Date )
					query += "'" + (new java.sql.Date(((Date)obj).getTime())).toString() +"'" + ",";
				else
					query += obj.toString() + ","; //TODO
			}
			//query = query.substring(0, query.length()-1); //remove last ','
			query += "'n'"; //newly added anno does not have a reply
			
			String type = "note";
			String temp = "dump"+am.getAnnotationBody();
			String rest = temp.split("<")[1]; //just after first '<'
			type = rest.split(" ")[0].length() == 0 ? rest.split(" ")[1] : rest.split(" ")[0];
			type = type.substring(0,Math.min(type.length(), 10));
			
			query += ",'"+type +"'"; //type
			
//			System.out.println("type: "+type);
						
						
			if(attr.get("rated") != null){
				int rate = 5;
				String rated = attr.get("rated");
				if(rated.equals("pos"))
					rate = 1;
				if(rated.equals("neg"))
					rate = 2;
				if(rated.equals("neut"))
					rate = 3;
				if(rated.equals("info"))
					rate = 4;
				if(rated.equals("nocat"))
					rate = 5;
				
				query += ","+rate;
			}
			
			if(snapshotId != -1)
				query += ","+snapshotId;
			
			query += ")";	
			
			System.out.println();
			System.out.println(query);
			System.out.println();
			
			statement.executeUpdate(query);
			
			statement.close();
			con.commit();

	   } catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();				
				//if(e.getErrorCode() == -4470 )					
				//FIXME I think I should restart db2 driver?				
				e = e.getNextException();
			}	
			return 2;
		} catch (Exception e) {			
			e.printStackTrace();
			return 2;
		} 
		return 0;
   }
   
   

   
   public ArrayList<AnnotationModel> findAnnosWithResourceUri(String resourceUri){
	   
	   ArrayList<AnnotationModel> annos = new ArrayList<AnnotationModel>();
	   
	   Connection con = DatabaseController.connectToDB();
	   Statement statement;
	   try {
			statement = con.createStatement();						
//			ResultSet result = statement.executeQuery("SELECT * FROM Annotation WHERE resourceUri = '"+ resourceUri +"'" );
//			ResultSet result = statement.executeQuery("SELECT x.*, y.name FROM Annotation x, Person y WHERE "+
//					"x.userid = y.username AND x.resourceUri = '"+ resourceUri +"'" );
			
			ResultSet result = statement.executeQuery("SELECT x.* FROM Annotation x, Resource y WHERE "+
					"x.resourceId = y.ID AND y.url = '"+ resourceUri +"'" );
			
			annos = extractAnnosFromResultSet(result, con, resourceUri);
			
			statement.close();
			con.commit();
			
		}catch(Exception e) {			
			e.printStackTrace();
		} 
		
	   return annos;
   }
   
   
   public ArrayList<AnnotationModel> findAnnosWithSameBib(String resourceUri){
	   ArrayList<AnnotationModel> annos = new ArrayList<AnnotationModel>();
	   
	   Connection con = DatabaseController.connectToDB();
	   Statement statement;
	   try {
			statement = con.createStatement();						
			
			ResultSet result = statement.executeQuery("WITH "+
						"bibsOfUrl (bibtexId) AS "+
							"(SELECT bibtexId "+
							"FROM Resource "+
								"WHERE url = '"+ resourceUri+"' "+
							") "+
						"SELECT x.* "+
						"FROM Annotation x, Resource y, bibsOfUrl b "+
						"WHERE x.resourceId = y.ID AND y.bibtexId = b.bibtexId	" );
			
			annos = extractAnnosFromResultSet(result, con, resourceUri);
			
			statement.close();
			con.commit();
			
		}catch(Exception e) {			
			e.printStackTrace();
		} 
		
	   return annos;
   }
   
   public ArrayList<AnnotationModel> findAnnosWithSameMd5Base64(String checksum){
	   ArrayList<AnnotationModel> annos = new ArrayList<AnnotationModel>();
	   
	   Connection con = DatabaseController.connectToDB();
	   Statement statement;
	   try {
			statement = con.createStatement();						
						
			ResultSet result = statement.executeQuery("SELECT x.* FROM Annotation x, Resource y WHERE "+
					"x.resourceId = y.ID AND y.documentDigest = '"+ checksum +"'" );
			
			annos = extractAnnosFromResultSet(result, con, null);
			
			statement.close();
			con.commit();
			
		}catch(Exception e) {			
			e.printStackTrace();
		} 
		
	   return annos;
   }
   
   public ArrayList<AnnotationModel> findAnnosWithSameMultivalentBase64Md5Text(String checksum){
	   ArrayList<AnnotationModel> annos = new ArrayList<AnnotationModel>();
	   
	   Connection con = DatabaseController.connectToDB();
	   Statement statement;
	   try {
			statement = con.createStatement();						
						
			ResultSet result = statement.executeQuery("SELECT x.* FROM Annotation x, Resource y WHERE "+
					"x.resourceId = y.ID AND y.documentTextDigest = '"+ checksum +"'" );
			
			annos = extractAnnosFromResultSet(result, con, null);
			
			statement.close();
			con.commit();
			
		}catch(Exception e) {			
			e.printStackTrace();
		} 
		
	   return annos;
   }
   
   private ArrayList<AnnotationModel> extractAnnosFromResultSet(ResultSet result, Connection con, String resourceUri) throws SQLException{
	   
	   ArrayList<AnnotationModel> annos = new ArrayList<AnnotationModel>();
		while( result.next() ){ 
			AnnotationModel am = new AnnotationModel();
			am.annotationBody = result.getString("annotationBody");
			am.annotationUri = result.getString("annotationUri");
			am.application = result.getString("application");
			/*am.author = result.getString("author");
			am.userid = am.author;*/
			
			
			int personId = result.getInt("personId");
			Statement st2 = con.createStatement();
			ResultSet resP = st2.executeQuery("SELECT username, name FROM Person WHERE ID = " + personId);
			resP.next();				
			
			am.userid = resP.getString("username");				
			am.author = resP.getString("name");
			st2.close();
			
			am.dateCreated = result.getDate("dateCreated");				
			am.dateModified = result.getDate("dateModified");
			
			int resourceId = result.getInt("resourceId");
			am.setResourceId(resourceId);
			
			Statement st3 = con.createStatement();
			ResultSet resD = st3.executeQuery("SELECT documentDigest, documentTextDigest FROM Resource WHERE ID = " + resourceId);
			resD.next();
			
			am.setDocumentDigest(resD.getString("documentDigest"));
			am.setDocumentTextDigest(resD.getString("documentTextDigest"));
			st3.close();
			
			am.id = result.getString("annoFABid");
			am.uniqueId = result.getInt("ID");				
			am.replyTo = result.getInt("replyTo");
			if( am.replyTo != null ) //TODO check
				am.isReplyToSth = true;
			am.lexicalSignature = result.getString("lexicalSignature");
			am.pageNumber = result.getInt("pageNumber") ;
			
			if( resourceUri == null ){
				Statement st4 = con.createStatement();				
				ResultSet res4 = st4.executeQuery("SELECT url FROM Resource WHERE ID = "+result.getInt("resourceId"));
				if(res4.next())
					am.resourceUri = res4.getString("url"); //resourceUri
				else
					am.resourceUri = "";
				st4.close();
			}
			else
				am.resourceUri = resourceUri;
			
			am.stringDescription = result.getString("stringDescription");
			//am.userid ;
			am.version = result.getString("version");
			
			annos.add(am);				
		}
		return annos;
   }
   
   /**
    * @param ID
    * @param username
    * @param pass
    * @return 0:ok, 1:not authenticated, 2:exception, 3:has reply
    */
   public int deleteAnnotation(Integer ID,String username, String pass){
	   
	   HashMap<String, String> auth = DatabaseController.personsMgmt.authenticate(username, pass);
	   int state = Integer.parseInt(auth.get("state"));
	   if( state != 0 )
		   return 1;
	   
	   Connection con = DatabaseController.connectToDB();
	   Statement statement;
	   try {
			statement = con.createStatement();				
			ResultSet result = statement.executeQuery("SELECT hasReply FROM Annotation WHERE ID = " + ID);
			if( result.next() ){
				if(result.getString("hasReply").equals("y"))
					return 3;
				statement.executeUpdate("DELETE FROM Annotation WHERE ID = "+ ID +" AND hasReply != 'y'" );
			}
		
			statement.close();
			con.commit();
			
		}catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();
				e = e.getNextException();
			}	
			return 2;
		} catch (Exception e) {			
			e.printStackTrace();
			return 2;
		} 
		return 0;
   }
   
   public int deleteAnnotationByFabId(String id, String username, String pass){
	   
	   HashMap<String, String> auth = DatabaseController.personsMgmt.authenticate(username, pass);
	   int state = Integer.parseInt(auth.get("state"));
	   if( state != 0 )
		   return 1;
	   
	   Connection con = DatabaseController.connectToDB();
	   Statement statement;
	   try {
			statement = con.createStatement();		
			ResultSet result = statement.executeQuery("SELECT hasReply FROM Annotation WHERE annoFABid = '" + id + "'");
			if( result.next() ){
				if(result.getString("hasReply").equals("y"))
					return 3;
				int rows = statement.executeUpdate("DELETE FROM Annotation WHERE annoFABid = '"+ id +"' AND hasReply != 'y'" );					
				if( rows == 0 )
					System.out.println("no output for this kind of query");
				else if (rows > 1)
					System.out.println("\nmore than one annotation deleted!! repeated annoFabIds... :((((\n");
			}
			statement.close();
			con.commit();
			
		}catch (SQLException e) {
			System.err.println("SQLException:");
			while(e!=null) {
				System.err.println ("Error msg: " + e.getMessage());
				System.err.println ("SQLSTATE: " + e.getSQLState());
				System.err.println ("Error code: " + e.getErrorCode());
				e.printStackTrace();
				e = e.getNextException();
			}
			return 2;
		} catch (Exception e) {			
			e.printStackTrace();
			return 2;
		} 
		return 0;
   }
   
   private AnnotationModel retrieveReplyOnInfo(AnnotationModel am, Connection con){
	 ///get replyon attributes:
	   HashMap<String,String> attr = ContentSerializer.parseAttributes(am.getAnnotationBody());
	   if(attr.containsKey("callout") || attr.containsKey("replyOnSth")
			   ||attr.containsKey("replyOnFabId") || attr.containsKey("replyOn")){
		   am.setReplyToSth(true);
//		   System.out.println("------------ reply on sth");
		   if(attr.containsKey("replyOnFabId"))
			   am.setReplyToFabId(attr.get("replyOnFabId"));
		   try{
			   if(attr.containsKey("replyOn"))
				   am.setReplyTo(Integer.parseInt(attr.get("replyOn")));
			   else
				   am.replyTo = null;
		   }catch(Exception e){
			   am.replyTo = null;
		   }
	   }
	   
	   if(am.isReplyToSth && am.replyTo == null && am.replyToFabId != null ){
		   try{
			   Statement statement = con.createStatement();
			   
			   ResultSet res = statement.executeQuery("SELECT ID FROM ANNOTATION WHERE annoFABid = '"+am.replyToFabId+"'");
			   if(res.next()){
				   am.replyTo = res.getInt("ID");
			   }
			   statement.close();
		   }
		   catch(SQLException e){
			   DB2SqlException.printSqlErrorMsg(e);
		   }
	   }
	   
	   return am;
   }



public void setRole(String role) {
	// TODO Auto-generated method stub
	
}



public void resetRole() {
	// TODO Auto-generated method stub
	
}
   
   
		  
}
